﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace CSharpConnectMySQL
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine(VerifyUser("1", "2"));
            Console.WriteLine(VerifyUser("shuai", "123456"));

            //ReadUserCount();
            //Delete();
            //Update();
            //Read();
            //Insert();
            Console.ReadKey();
        }


        static bool VerifyUser(string username, string password)
        {
            string connectStr = "server=localhost;port=3306;database=sky;user=root;password=123456;";
            MySqlConnection conn = new MySqlConnection(connectStr);

            try
            {
                conn.Open();
                Console.WriteLine("数据库连接成功");

                string sql = "select * from sky.user where username = '"+username+"' and password = '"+password+"';";
                MySqlCommand cmd = new MySqlCommand(sql, conn);


                MySqlDataReader reader = cmd.ExecuteReader();
                
                if (reader.Read())
                {
                    return true;
                }
                
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            return false;
        }

        //修改
        static void ReadUserCount()
        {
            string connectStr = "server=localhost;port=3306;database=sky;user=root;password=123456;";
            MySqlConnection conn = new MySqlConnection(connectStr);

            try
            {
                conn.Open();
                Console.WriteLine("数据库连接成功");
                
                string sql = "select count(*) FROM `sky`.`user`;";
                MySqlCommand cmd = new MySqlCommand(sql, conn);



                object o = cmd.ExecuteScalar();
                int count = Convert.ToInt32(o.ToString());


                //MySqlDataReader reader = cmd.ExecuteReader();
                //reader.Read();

                //int count = Convert.ToInt32(reader[0].ToString());
                Console.WriteLine(count);

            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }

        //修改
        static void Delete()
        {
            string connectStr = "server=localhost;port=3306;database=sky;user=root;password=123456;";
            MySqlConnection conn = new MySqlConnection(connectStr);

            try
            {
                conn.Open();
                Console.WriteLine("数据库连接成功");
                
                string sql = "DELETE FROM `sky`.`user` WHERE (`id` = '100003');";
                MySqlCommand cmd = new MySqlCommand(sql, conn);

                int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据条数

            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }

        //修改
        static void Update()
        {
            string connectStr = "server=localhost;port=3306;database=sky;user=root;password=123456;";
            MySqlConnection conn = new MySqlConnection(connectStr);

            try
            {
                conn.Open();
                Console.WriteLine("数据库连接成功");
                
                string sql = "UPDATE `sky`.`user` SET `username` = '222' WHERE (`id` = '100005');";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                
                int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据条数
                
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }

        //插入
        static void Insert()
        {
            string connectStr = "server=localhost;port=3306;database=sky;user=root;password=123456;";
            MySqlConnection conn = new MySqlConnection(connectStr);

            try
            {
                conn.Open();
                Console.WriteLine("数据库连接成功");

                string sql = "insert into sky.user(id,username,password) values(100005, 'a1', '"+DateTime.Now+"')";
                MySqlCommand cmd = new MySqlCommand(sql, conn);

                int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据条数

                Console.WriteLine("返回值是数据库中受影响的数据条数:" + result);

            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());

            }
        }

        //读取
        static void Read()
        {
            string connectStr = "server=localhost;port=3306;database=sky;user=root;password=123456;";
            MySqlConnection conn = new MySqlConnection(connectStr);

            try
            {
                conn.Open();
                Console.WriteLine("数据库连接成功");

                string sql = "SELECT * FROM sky.user;";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                //cmd.ExecuteReader();//查询
                //cmd.ExecuteNonQuery();
                //cmd.ExecuteScalar();

                MySqlDataReader reader = cmd.ExecuteReader();

                int i = 0;
                while (reader.Read())
                {
                    //Console.WriteLine(i + 1 + ": " + reader[0].ToString() + " - " + reader[1].ToString() + " - " + reader[2].ToString());
                    Console.WriteLine(i + 1 + ": " + reader.GetInt32("id") + " - " + reader.GetString("username") + " - " + reader.GetString("password"));
                    i++;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }
        

    }
}